﻿using Dapper;
using DevExpress.Utils.Drawing;
using FirebirdSql.Data.FirebirdClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static DevExpress.XtraEditors.Mask.MaskSettings;
using static System.Net.WebRequestMethods;

namespace DBOptionPro.DBUtils
{
    public abstract class DBHelper<T> : IDisposable
    {
        /// <summary>
        /// 构造
        /// </summary>
        /// <param name="dbpath">数据库路径</param>
        public DBHelper(string dbpath)
        {
            string dbtype = Path.GetExtension(dbpath);
            if (dbtype.ToLower() == ".mdb")
            {
                _DBConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbpath + ";Persist Security Info=False");
            }
            else if (dbtype.ToLower() == ".fdb")
            {
                FbConnectionStringBuilder connBuilder = new FbConnectionStringBuilder();
                connBuilder.UserID = "SYSDBA";
                connBuilder.ServerType = FbServerType.Embedded;//设置数据库类型为 嵌入式；
                connBuilder.Database = dbpath;//数据库文件的目录；
                _DBConnection = new FbConnection(connBuilder.ToString());
            }
            else if (dbtype.ToLower() == ".dbf")
            {
                _DBConnection = new OleDbConnection(@"Provider=VFPOLEDB.1;Data Source=" + dbpath + ";Collating Sequence=MACHINE");

            }
            else
            {
                _DBConnection = null;
            }
        }

        /// <summary>
        /// 数据库连接对象
        /// </summary>
        protected readonly IDbConnection _DBConnection;

        /// <summary>
        /// 查询集合
        /// </summary>
        /// <param name="querySql">sql语句</param>
        /// <param name="queryPars">查询参数，sql语句中的参数逆名对象</param>
        /// <returns></returns>
        public virtual List<T> QueryList(string querySql, object queryPars)
        {
            if (queryPars == null)
            {
                return _DBConnection.Query<T>(querySql).ToList();
            }
            else
            {
                return _DBConnection.Query<T>(querySql, queryPars).ToList();

            }
        }

        /// <summary>
        /// 查询单个
        /// </summary>
        /// <param name="querySql">sql语句</param>
        /// <param name="queryPars">查询参数，sql语句中的参数逆名对象</param>
        /// <returns></returns>
        public virtual T QuerySingle(string querySql, object queryPars)
        {
            return _DBConnection.QueryFirst<T>(querySql, queryPars);
        }

        /// <summary>
        /// 插入一个对象
        /// </summary>
        /// <param name="insertSql">插入语句</param>
        /// <param name="obj">插入对象</param>
        /// <returns></returns>
        public virtual bool InsertOne(string insertSql, T obj)
        {
            return _DBConnection.Execute(insertSql, obj) == 1;
        }

        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="insertSql">sql语句</param>
        /// <param name="ls">插入对象集合</param>
        /// <returns></returns>
        public virtual bool InsertList(string insertSql, List<T> ls)
        {
            return _DBConnection.Execute(insertSql, ls) == ls.Count;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="deleteSql">删除语句</param>
        /// <param name="deletePars">删除参数，sql语句中的参数逆名对象</param>
        public virtual void Delete(string deleteSql, object deletePars)
        {
            _DBConnection.Execute(deleteSql, deletePars);
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="updateSql">更新SQL语句</param>
        /// <param name="updatePars">更新参数，sql语句中的参数逆名对象</param>
        /// <returns></returns>
        public virtual bool Update(string updateSql, object updatePars)
        {
            return _DBConnection.Execute(updateSql, updatePars) >= 1;
        }

        /// <summary>
        /// 执行Sql语句
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="pars">sql语句中的参数逆名对象</param>
        /// <returns></returns>
        public virtual int ExcuteSql(string sql, object pars)
        {
            return _DBConnection.Execute(sql, pars);
        }

        public virtual DataTable GetDataTable(string sql, object pars = null)
        {
            DataTable dt = new DataTable();
            var ret = _DBConnection.ExecuteReader(sql, pars);
            dt.Load(ret);
            return dt;
        }

        public void Dispose()
        {
            _DBConnection?.Close();
        }
    }
}
